Pro Entity Framework Core 2 for ASP.NET Core MVC 翻译

第 23 章使用数据库服务器功能

作者：Adam Freeman
翻译：陈广
日期：2019-6-6

即便有高级功能，Entity Framework Core 还致力于涵盖数据库服务器提供的最普遍需要的功能。但是，仅知道这些对某些项目是不够的，Microsoft 还包含了对直接使用数据库服务器的支持，如果您的项目有专门的需求，或者您正在建模一个复杂的数据库，这将是非常宝贵的。

在本章中，我将演示如何直接使用 SQL 访问 Entity Framework Core 不直接支持的功能，包括视图、存储过程和表值函数。我还演示了 Entity Framework Core 为处理数据库服务器生成的数据值提供的一系列功能。表23-1为本章简述。

警告：所有 Entity Framework Core 功能都会在数据库服务器和提供程序包之间发生变化，但本章中的例子尤其如此。这些示例已经使用 SQL Server 和默认的 Microsoft 数据库提供程序进行了测试。如果使用的是不同的数据库服务器或提供程序包，则可能必须对示例进行更改。

表 23-1：数据库功能简述

问题	回答
它们是什么？	数据库服务器提供了使用常规 Entity Framework Core 技术无法访问的高级功能。Entity Framework Core 包括一组用于处理这些功能的工具，这样您就可以越过所有数据库服务器共有的基本功能集。
它们有何用途？	这些功能对于增加数据库服务器代表应用程序进行的工作量，或者当您需要其他无法访问的特定功能时，都非常有用。如果使用的是现有数据库，则可能必须使用这些功能来获取和存储数据。
如何使用它们	这些特性是通过 LINQ 查询中包含的方法和配置数据模型的 Fluent API 语句的组合来访问的。
是否有任何缺陷或限制？	直接使用数据库功能会使测试应用程序代码变得更加困难，并且需要很好地理解高级数据库服务器功能以及使用它们所需的 SQL。
有没有其他选择？	没有，本章中描述的技术是访问数据库服务器功能的唯一方法。

表 23-2 为本章摘要。

表23-2：本章摘要

问题	解决方案	清单
执行 SQL 命令	使用`FromSql`或`ExecuteSqlCommand`方法	1-18
更新数据模型对象以反映服务器生成的值	使用`HasDefaultValueSql`或`HasSequence`方法或`ValueGeneratedOnXXX`方法	19-25，30-33
确保值为唯一的	使用`HasIndex`方法	26-29

准备本章

在本章中，我继续使用自第19章以来一直使用的 AdvancedApp 项目。本章中的一些查询不包括关联数据，因此我更改了 Home 控制器使用的 Index 视图，如清单23-1所示。

提示：如果您不想跟随构建示例项目的过程，可以从本书的源代码库下载所有所需的文件，这些文件可在 https://github.com/apress/pro-ef-core-2-for-asp.net-core-mvc 上找到。

清单 23-1：Views/Home 文件夹下的 Index.cshtml 文件，处理缺少的关联数据

@model IEnumerable<Employee>
@{
    ViewData["Title"] = "Advanced Features";
    Layout = "_Layout";
}
<h3 class="bg-info p-2 text-center text-white">Employees</h3>
<table class="table table-sm table-striped">
    <thead>
        <tr>
            <th>SSN</th>
            <th>First Name</th>
            <th>Family Name</th>
            <th>Salary</th>
            <th>Other Name</th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <tr class="placeholder"><td colspan="8" class="text-center">No Data</td></tr>
        @foreach (Employee e in Model)
        {
            <tr>
                <td>@e.SSN</td>
                <td>@e.FirstName</td>
                <td>@e.FamilyName</td>
                <td>@e.Salary</td>
                <td>@(e.OtherIdentity?.Name ?? "(None)")</td>
                <td class="text-right">
                    <form>
                        <input type="hidden" name="SSN" value="@e.SSN" />
                        <input type="hidden" name="Firstname" value="@e.FirstName" />
                        <input type="hidden" name="FamilyName"
                               value="@e.FamilyName" />
                        <input type="hidden" name="RowVersion"
                               asp-for="@e.RowVersion" />
                        <input type="hidden" name="OtherIdentity.Id"
                               value="@e.OtherIdentity?.Id" />
                        <button type="submit" asp-action="Delete" formmethod="post"
                                class="btn btn-sm btn-danger">
                            Delete
                        </button>
                        <button type="submit" asp-action="Edit" formmethod="get"
                                class="btn btn-sm btn-primary">
                            Edit
                        </button>
                    </form>
                </td>
            </tr>
        }
    </tbody>
</table>
@if (ViewBag.Secondaries != null)
{
    @await Html.PartialAsync("SecondaryIdentities");
}
<div class="text-center">
    <a asp-action="Edit" class="btn btn-primary">Create</a>
</div>

我还需要在没有查询过滤器的情况下开始本章，因此我在过滤软删除对象的 context 类中注释掉了 Fluent API 语句，如清单23-2所示。

清单 23-2：Models 文件夹下的 AdvancedContext.cs 文件，禁用查询过滤器

using Microsoft.EntityFrameworkCore;
using System;

namespace AdvancedApp.Models
{
    public class AdvancedContext : DbContext
    {
        public AdvancedContext(DbContextOptions<AdvancedContext> options)
            : base(options) { }
        public DbSet<Employee> Employees { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            //modelBuilder.Entity<Employee>()
            // .HasQueryFilter(e => !e.SoftDeleted);
            modelBuilder.Entity<Employee>().Ignore(e => e.Id);
            modelBuilder.Entity<Employee>()
                .HasKey(e => new { e.SSN, e.FirstName, e.FamilyName });
            modelBuilder.Entity<Employee>()
                .Property(e => e.Salary).HasColumnType("decimal(8,2)")
                .HasField("databaseSalary")
                .UsePropertyAccessMode(PropertyAccessMode.Field);
            modelBuilder.Entity<Employee>().Property<DateTime>("LastUpdated")
                .HasDefaultValue(new DateTime(2000, 1, 1));
            modelBuilder.Entity<Employee>()
                .Property(e => e.RowVersion).IsRowVersion();
            modelBuilder.Entity<SecondaryIdentity>()
                .HasOne(s => s.PrimaryIdentity)
                .WithOne(e => e.OtherIdentity)
                .HasPrincipalKey<Employee>(e => new {
                    e.SSN,
                    e.FirstName,
                    e.FamilyName
                })
                .HasForeignKey<SecondaryIdentity>(s => new {
                    s.PrimarySSN,
                    s.PrimaryFirstName,
                    s.PrimaryFamilyName
                })
                .OnDelete(DeleteBehavior.Restrict);
            modelBuilder.Entity<SecondaryIdentity>()
                .Property(e => e.Name).HasMaxLength(100);
        }
    }
}

本章中的一些示例需要客户端对查询进行评估，因此我注释掉了 Startup 类中的语句，该语句告诉 Entity Framework Core 在应用程序中计算部分查询时抛出异常，如清单23-3所示。

清单 23-3：AdvancedApp 文件夹下的 Startup.cs 文件，禁用客户端异常

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Configuration;
using Microsoft.EntityFrameworkCore;
using AdvancedApp.Models;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace AdvancedApp
{
    public class Startup
    {
        public Startup(IConfiguration config) => Configuration = config;
        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            string conString = Configuration["ConnectionStrings:DefaultConnection"];
            services.AddDbContext<AdvancedContext>(options =>
            options.UseSqlServer(conString));
            //.ConfigureWarnings(warning => warning.Throw(
            // RelationalEventId.QueryClientEvaluationWarning)));
        }

        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            app.UseDeveloperExceptionPage();
            app.UseStatusCodePages();
            app.UseStaticFiles();
            app.UseMvcWithDefaultRoute();
        }
    }
}

接下来，在 AdvancedApp 项目文件夹下运行清单23-4所示的命令，删除并重建数据库。

清单 23-4：删除并重建数据库

dotnet ef database drop --force
dotnet ef database update

使用dotnet run启动应用程序，导航至 http://localhost:5000，单击【Create】按钮，使用表 23-3 所示的值存储三个Employee对象。

表 23-3：创建示例对象所需的数据值

SSN	FirstName	FamilyName	Salary	Other Name	In Active Use
420-39-1864	Bob	Smith	100000	Robert	Checked
657-03-5898	Alice	Jones	200000	Allie	Checked
300-30-0522	Peter	Davies	180000	Pette	Checked

创建完所有三个对象后，您将看到图23-1所示的布局。

图23-1 运行示例应用程序

直接使用 SQL

Entity Framework Core 很好地提供了对大多数项目所需的数据库服务器功能的访问，但是每个数据库服务器都有独特的功能，使用它们意味着直接使用 SQL。在接下来的部分中，我将解释 Entity Framework Core 支持使用 SQL 的不同方式，并演示每个方法是如何工作的。

警告：只有在无法使用标准 Entity Framework Core 功能获得所需的结果时，才应该使用这些特性。直接使用 SQL 会使您的应用程序更难测试和维护，并且会限制您的应用程序，导致它只能与一个数据库服务器一起工作。如果不使用 SQL Server，则可能无法从本章的示例中获得预期的结果。

使用 SQL 查询

Entity Framework Core 支持直接使用 SQL 查询数据库的FromSql方法。为了演示，我更改了 Home 控制器的Index action，以便它使用 SQL 查询数据库，如清单23-5所示。

清单 23-5：Controllers 文件夹下的 HomeController.cs 文件，使用 SQL 查询

using AdvancedApp.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System;
using System.Collections.Generic;

namespace AdvancedApp.Controllers
{
    public class HomeController : Controller
    {
        private AdvancedContext context;
        public HomeController(AdvancedContext ctx) => context = ctx;

        public IActionResult Index()
        {
            IEnumerable<Employee> data = context.Employees
                .FromSql(@"SELECT * FROM Employees
                    WHERE SoftDeleted = 0
                    ORDER BY Salary DESC");
            //ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
            return View(data);
        }

        public IActionResult Edit(string SSN, string firstName, string familyName)
        {
            return View(string.IsNullOrWhiteSpace(SSN)
                ? new Employee() : context.Employees.Include(e => e.OtherIdentity)
                    .First(e => e.SSN == SSN
                        && e.FirstName == firstName
                        && e.FamilyName == familyName));
        }

        [HttpPost]
        public IActionResult Update(Employee employee)
        {
            if (context.Employees.Count(e => e.SSN == employee.SSN
                && e.FirstName == employee.FirstName
                && e.FamilyName == employee.FamilyName) == 0)
            {
                context.Add(employee);
            }
            else
            {
                Employee e = new Employee
                {
                    SSN = employee.SSN,
                    FirstName = employee.FirstName,
                    FamilyName = employee.FamilyName,
                    RowVersion = employee.RowVersion
                };
                context.Employees.Attach(e);
                e.Salary = employee.Salary;
                e.LastUpdated = DateTime.Now;
            }
            context.SaveChanges();
            return RedirectToAction(nameof(Index));
        }

        [HttpPost]
        public IActionResult Delete(Employee employee)
        {
            context.Employees.Attach(employee);
            employee.SoftDeleted = true;
            context.SaveChanges();
            return RedirectToAction(nameof(Index));
        }
    }
}

FromSql方法用于创建包含原始 SQL 的查询，在示例中，我从未被软删除的 Employees 表中选择数据，并按Salary值对结果进行排序。

对于FromSql方法的使用，在SQL中有一些限制。首先，也是最重要的，您必须确保您的结果包含 Entity Framework Core 将创建的实体类的每个属性的列，并且这些列必须具有与这些属性相同的名称。您只能通过其DbSet<T>查询特定实体类，并且不能包含关联数据，这意味着除非类是数据模型的一部分，否则不能查询类。最后， Entity Framework Core 不会创建关联对象，即使在原始 SQL 查询中包含一个JOIN。（有关获取关联数据的不同技术的详细信息，请参阅《组合复杂查询》部分。）

要查看发送到数据库服务器的 SQL，请使用dotnet run启动应用程序，然后导航到 http://localhost:5000。检查应用程序生成的日志消息，您将看到与清单23-5中的原始 SQL 相对应的查询。

...
SELECT * FROM Employees
WHERE SoftDeleted = 0
ORDER BY Salary DESC
...

由于没有从数据库检索到关联数据，因此将只向用户显示Employee对象的详细信息，如图23-2所示。

图23-2 使用原始 SQL 查询数据库

查询中使用参数

若要防止 SQL 注入攻击，必须将 SQL 查询中包含的任何用户输入参数化。最简单的方法是依赖字符串内插特性，该特性根据 SQL 字符串中的名称包含值，并自动确保这些值被安全地处理。在清单23-6中，我已经更改了Index action 所做的查询，以便它接受一个参数，该参数的值来自 HTTP 请求并用于选择数据。

清单 23-6：Controllers 文件夹下的 HomeController.cs 文件，使用查询参数

...
public IActionResult Index(decimal salary = 0)
{
    IEnumerable<Employee> data = context.Employees
        .FromSql($@"SELECT * FROM Employees
            WHERE SoftDeleted = 0
            AND Salary > {salary}
            ORDER BY Salary DESC");
    //ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
    return View(data);
}
...

以美元符号作为前缀的字符串允许我按名称包含值，这样{salary}就可以安全地将值合并到查询中。要查看生成的 SQL，请使用dotnet run启动应用程序，然后导航到 http://localhost:5000。在应用程序生成的日志消息中，您将看到此查询：

...
SELECT * FROM Employees
WHERE SoftDeleted = 0 AND Salary > @p0
ORDER BY Salary DESC
...

Entity Framework Core 发送了一个使用安全参数的查询，而不是将值直接合并到 SQL 字符串中。如果导航到 http://localhost:5000?salary=100000，您将看到数据被过滤，以便只显示工资值超过100,000的Employee对象，如图23-3所示。

图23-3 使用用户输入进行查询

在查询中传递参数的其他方法

如果不想使用字符串插值，则可以将参数安全地传递为FromSql方法的参数，如下：

...
context.Employees.FromSql(@"SELECT * FROM Employees
        WHERE SoftDeleted = 0 AND Salary > {0}
        ORDER BY Salary DESC", salary);
...

使用基于0的索引在 SQL 字符串中指定和引用这些参数。如果您正在使用包含查询所需的参数名称的字符串，则可以使用SqlParameter类为查询提供值。

...
SqlParameter min = new SqlParameter("minSalary", salary);

IEnumerable<Employee> data = context.Employees
    .FromSql(@"SELECT * FROM Employees
    WHERE SoftDeleted = 0 AND Salary > @minSalary
    ORDER BY Salary DESC", min);
...

这两种技术都产生与清单23-6所示的字符串插值技术相匹配的查询。

组合复杂查询

如果您的数据库服务器和提供程序包支持它，那么 Entity Framework Core 可以使用原始 SQL 作为更复杂查询的基础，该查询使用标准的 LINQ 方法或其他 Entity Framework Core 功能组成。例如，我取消了 context 类中的 Fluent API 语句，以启用排除软删除对象的查询过滤器，如清单23-7所示。

清单 23-7：Models 文件夹下的 AdvancedContext.cs 文件，启用查询过滤器

using Microsoft.EntityFrameworkCore;
using System;

namespace AdvancedApp.Models
{
    public class AdvancedContext : DbContext
    {
        public AdvancedContext(DbContextOptions<AdvancedContext> options)
            : base(options) { }
        public DbSet<Employee> Employees { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            modelBuilder.Entity<Employee>() //此处解除注释
             .HasQueryFilter(e => !e.SoftDeleted);

            modelBuilder.Entity<Employee>().Ignore(e => e.Id);
            modelBuilder.Entity<Employee>()
                .HasKey(e => new { e.SSN, e.FirstName, e.FamilyName });
            
            // ...其它省略...

        }
    }
}

在清单23-8中，我更改了Index action 方法中的查询，以便包含关联数据，并使用LastUpdated属性对结果进行排序。

清单 23-8：Controllers 文件夹下的 HomeController.cs 文件，组合复杂查询

...
public IActionResult Index(decimal salary = 0)
{
    IEnumerable<Employee> data = context.Employees
        .FromSql($@"SELECT * FROM Employees
            WHERE SoftDeleted = 0
            AND Salary > {salary}")
        .Include(e => e.OtherIdentity)
        .OrderByDescending(e => e.Salary)
        .OrderByDescending(e => e.LastUpdated).ToArray();
    ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
    return View(data);
}
...

Entity Framework Core 生成一个查询，该查询将传递给FromSql方法的原始 SQL 与表示 LINQ 方法的附加查询相结合。若要查看组合查询，请使用dotnet run启动应用程序，导航到 http://localhost:5000?salary=100000，并检查日志消息，其中将包含此查询：

...
SELECT [e].[SSN], [e].[FirstName], [e].[FamilyName], [e].[LastUpdated],
    [e].[RowVersion], [e].[Salary], [e].[SoftDeleted], [e.OtherIdentity].[Id],
    [e.OtherIdentity].[InActiveUse], [e.OtherIdentity].[Name],
    [e.OtherIdentity].[PrimaryFamilyName], [e.OtherIdentity].[PrimaryFirstName],
    [e.OtherIdentity].[PrimarySSN]
FROM (
    SELECT * FROM Employees
    WHERE SoftDeleted = 0 AND Salary > @p0
) AS [e]
LEFT JOIN [SecondaryIdentity] AS [e.OtherIdentity] ON
    (([e].[SSN] = [e.OtherIdentity].[PrimarySSN])
    AND ([e].[FirstName] = [e.OtherIdentity].[PrimaryFirstName]))
    AND ([e].[FamilyName] = [e.OtherIdentity].[PrimaryFamilyName])
WHERE [e].[SoftDeleted] = 0
ORDER BY [e].[LastUpdated] DESC, [e].[Salary] DESC
...

查询的内部部分是我传递给FromSql方法的字符串，该字符串被一个外部查询包围，该查询使用SELECT获取 Entity Framework Core 创建Employee和SecondaryIdentity对象所需的列名、用于获取关联数据的联接、排除软删除数据的WHERE子句以及将根据LastUpdated值对数据进行排序的ORDER BY子句。

与使用原始 SQL 创建的查询相比，原始 SQL 和标准 LINQ 方法的混合可以使查询更易于管理和测试。也就是说，这种技术确实限制了查询的原始 SQL 部分。例如，请注意，我已经将基于Salary值选择数据的WHERE子句移到清单23-8中的 LINQ OrderByDescending方法中。组合查询中对原始 SQL 的限制之一是不能使用ORDER BY子句。

使用原始 SQL 查询存储过程

在 ASP.NET Core MVC 应用程序出现之前使用的数据库，或者在有特定性能或数据管理需求的情况下，经常会遇到存储过程。Entity Framework Core 支持查询存储过程，尽管数据库服务器或提供程序可能对可以进行的查询类型设置限制。

清单23-9包含创建简单存储过程所需的 SQL 语句，该过程查询数据库中的Salary值超过指定金额的Employee对象。从 Visual Studio 【工具】菜单中选择【SQL Server】➤【New Query】，连接到数据库，并执行 SQL 以创建存储过程。

提示：您可以下载一个包含清单23-9中的 SQL 语句的文件，作为随书一起下载的免费源代码的一部分 https://github.com/apress/pro-ef-core-2-for-asp.net-core-mvc。

清单 23-9：一个简单的存储过程

USE AdvancedDb
GO

DROP PROCEDURE IF EXISTS GetBySalary;
GO

CREATE PROCEDURE GetBySalary
	@SalaryFilter decimal
AS
	SELECT * from Employees
	WHERE Salary > @SalaryFilter AND SoftDeleted = 0
	ORDER BY Salary DESC
GO

Entity Framework Core 无法使用其标准功能查询存储过程，这意味着必须使用FromSql方法。在清单23-10中，我已在 Home 控制器的Index action 中更改了查询，以便它使用存储的过程查询数据库。

清单 23-10：Controllers 文件夹下的 HomeController.cs 文件，使用存储过程查询数据库

...
public IActionResult Index(decimal salary = 0)
{
    IEnumerable<Employee> data = context.Employees
        .FromSql($"Execute GetBySalary @SalaryFilter = {salary}")
        .IgnoreQueryFilters();
    //ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
    return View(data);
}
...

可以使用存储过程执行的查询类型是有限的。例如，不能包含关联数据，这就是我删除Include的原因。我已经删除了OrderByDescending方法，并添加了用来阻止客户端执行的IgnoreQueryFilters方法。Entity Framework Core 不能使用存储过程组合复杂的查询，因此必须确保该过程执行所需的所有筛选和处理，或者接受可能需要的客户端计算。若要测试存储过程查询，请使用dotnet run启动应用程序并导航到 http://localhost:5000?salary=150000。如果检查应用程序生成的日志消息，可以看到存储过程使用如下查询：

...
Execute GetHighEarners @SalaryFilter = @p0
...

由于没有相关数据，结果将只包括Employee数据，如图23-4所示。

图23-4 使用存储过程查询

使用视图组合复杂查询

如果您能够影响或更改数据库的设计，那么 Entity Framework Core 对视图有更灵活的支持，视图是虚拟表其内容通过查询生成。清单23-11包含创建一个简单视图所需的 SQL 语句，该视图包含所有尚未被软删除的雇员数据。视图通常更复杂，或者通过执行计算来合成数据，但是这个简单的视图足以演示如何将它们与 Entity Framework Core 一起使用。在 Visual Studio 【工具】菜单中选择【SQL Server】➤【New Query】，连接数据库，并执行 SQL 创建视图。

清单 23-11：一个简单视图

USE AdvancedDb
GO

DROP VIEW IF EXISTS NotDeletedView
GO

CREATE VIEW NotDeletedView
AS
	SELECT * FROM Employees
	WHERE SoftDeleted = 0
GO

为了查询视图，我更新了 Home 控制器上的Index action，如清单23-12所示。由于视图返回表，Entity Framework Core 可以通过混合原始 SQL 和普通 LINQ 查询方法来组合查询。

清单 23-12：Controllers 文件夹下的 HomeController.cs 文件，查询视图

...
public IActionResult Index(decimal salary = 0)
{
    IEnumerable<Employee> data = context.Employees
        .FromSql($@"SELECT * from NotDeletedView
            WHERE Salary > {salary}")
        .Include(e => e.OtherIdentity)
        .OrderByDescending(e => e.Salary)
        .OrderByDescending(e => e.LastUpdated)
        .IgnoreQueryFilters()
        .ToArray();
    ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
    return View(data);
}
...

视图是查询中数据的来源，并由Include和OrderByDescending方法作为补充。我添加了IgnoreQueryFilters方法，因为视图已经排除了软删除的数据，使得查询过滤器变得多余。启动应用程序，导航到 http://localhost:5000，并检查日志消息，以查看发送到数据库服务器的组合查询。

...
SELECT [e].[SSN], [e].[FirstName], [e].[FamilyName], [e].[LastUpdated],
    [e].[RowVersion], [e].[Salary], [e].[SoftDeleted], [e.OtherIdentity].[Id],
    [e.OtherIdentity].[InActiveUse], [e.OtherIdentity].[Name],
    [e.OtherIdentity].[PrimaryFamilyName], [e.OtherIdentity].[PrimaryFirstName],
    [e.OtherIdentity].[PrimarySSN]
FROM (SELECT * from NotDeletedView WHERE Salary > @p0) AS [e]
LEFT JOIN [SecondaryIdentity] AS [e.OtherIdentity]
    ON (([e].[SSN] = [e.OtherIdentity].[PrimarySSN])
    AND ([e].[FirstName] = [e.OtherIdentity].[PrimaryFirstName]))
    AND ([e].[FamilyName] = [e.OtherIdentity].[PrimaryFamilyName])
ORDER BY [e].[LastUpdated] DESC, [e].[Salary] DESC
...

由于组合查询包含关联数据，用户将同时显示Employee和SecondaryIdentity对象，如图23-5所示。

图23-5 使用视图查询

使用表值函数组合复杂查询

表值函数就像视图和存储过程之间的交汇点。与存储过程一样，表值函数可以接受参数，并生成一个表作为其结果，就像视图一样。当使用 Entity Framework Core 时，表值函数通常可以替换存储过程，其优点是可以组合复杂的查询。

清单23-13包含创建表值函数所需的 SQL 语句，该函数执行与清单23-9中创建的存储过程相同的查询。在 Visual Studio 【工具】菜单下选择【SQL Server】➤【New Query】，连接至数据库并执行创建函数的 SQL。

提示：Entity Framework Core 还支持标量函数，它是返回单个值的函数，而不是表值函数生成的结果表。与直接使用 SQL 不同，您可以在 context 类中创建一个方法，Entity Framework Core 知道查询中对该方法的调用将导致函数的执行。在编写本报告时，此功能是有限的，只适用于数量有限的结果类型。在线搜索 Fluent API HasDbFunction方法以获得详细信息。

清单 23-13：表值函数

USE AdvancedDb
GO

DROP FUNCTION IF EXISTS GetSalaryTable
GO

CREATE FUNCTION GetSalaryTable(@SalaryFilter decimal)
RETURNS @employeeInfo TABLE
(
	SSN nvarchar(450),
	FirstName nvarchar(450),
	FamilyName nvarchar(450),
	Salary decimal(8, 2),
	LastUpdated datetime2(7),
	SoftDeleted bit
) AS
	BEGIN
		INSERT INTO @employeeInfo
		SELECT SSN, FirstName, FamilyName, Salary, LastUpdated, SoftDeleted
		FROM Employees
		WHERE Salary > @SalaryFilter AND SoftDeleted = 0
			ORDER BY Salary DESC
		RETURN
	END
GO

此SQL创建一个名为GetSalaryTable的函数，该函数返回 Entity Framework Core 创建Employee对象所需的几乎所有列的表。除了RowVersion列之外，因为它的 SQL 数据类型不能在函数中使用。

如前所述，原始 SQL 查询必须为 Entity Framework Core 创建对象所需的所有属性生成值。为了防止 Entity Framework Core 处理查询结果时出现异常，我将数据模型配置为忽略Employee类中的RowVersion属性，如清单23-14所示。

警告：确保您理解忽略属性对此类查询工作的影响。在这种情况下，当用户软删除对象时，禁用RowVersion属性将禁用对并发更新的保护。其他操作不受影响，因为 Home 控制器中的其他 action 方法所做的查询不使用表值函数。

清单 23-14：Models 文件夹下的 AdvancedContext.cs 文件，忽略属性

using Microsoft.EntityFrameworkCore;
using System;

namespace AdvancedApp.Models
{
    public class AdvancedContext : DbContext
    {
        public AdvancedContext(DbContextOptions<AdvancedContext> options)
            : base(options) { }
        public DbSet<Employee> Employees { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            modelBuilder.Entity<Employee>() //此处解除注释
             .HasQueryFilter(e => !e.SoftDeleted);
            modelBuilder.Entity<Employee>().Ignore(e => e.Id);
            modelBuilder.Entity<Employee>()
                .HasKey(e => new { e.SSN, e.FirstName, e.FamilyName });
            modelBuilder.Entity<Employee>()
                .Property(e => e.Salary).HasColumnType("decimal(8,2)")
                .HasField("databaseSalary")
                .UsePropertyAccessMode(PropertyAccessMode.Field);
            modelBuilder.Entity<Employee>().Property<DateTime>("LastUpdated")
                .HasDefaultValue(new DateTime(2000, 1, 1));

            modelBuilder.Entity<Employee>() //此处更改
                .Ignore(e => e.RowVersion);
             // .Property(e => e.RowVersion).IsRowVersion();

            modelBuilder.Entity<SecondaryIdentity>()
                .HasOne(s => s.PrimaryIdentity)
                .WithOne(e => e.OtherIdentity)
                .HasPrincipalKey<Employee>(e => new {
                    e.SSN,
                    e.FirstName,
                    e.FamilyName
                })
                .HasForeignKey<SecondaryIdentity>(s => new {
                    s.PrimarySSN,
                    s.PrimaryFirstName,
                    s.PrimaryFamilyName
                })
                .OnDelete(DeleteBehavior.Restrict);
            modelBuilder.Entity<SecondaryIdentity>()
                .Property(e => e.Name).HasMaxLength(100);
        }
    }
}

为了使用表值函数，我在 Home 控制器的Index action 中更改了查询，如清单23-15所示。

清单 23-15：Controllers 文件夹下的 HomeController.cs 文件，查询表值函数

public IActionResult Index(decimal salary = 0)
{
    IEnumerable<Employee> data = context.Employees
        .FromSql($@"SELECT * from GetSalaryTable({salary})")
        .Include(e => e.OtherIdentity)
        //.OrderByDescending(e => e.Salary)
        .OrderByDescending(e => e.LastUpdated)
        .IgnoreQueryFilters()
        .ToArray();
    ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
    return View(data);
}

表值函数用作原始 SQL 查询的目标，并接收用于按Salary值进行筛选的参数。由于 Entity Framework Core 能够在使用表值函数时执行复杂的查询，所以我能够包含关联数据并对结果进行排序。（由于函数已经使用Salary值对数据进行排序，所以我已经注释掉了Salary属性的OrderByDescending。）

要查看效果，请使用dotnet run启动应用程序，运行并导航到 http://localhost:5000。在应用程序生成的日志消息中，您将看到 Entity Framework Core 为针对表值函数而组成的查询。

...
SELECT [e].[SSN], [e].[FirstName], [e].[FamilyName], [e].[LastUpdated], [e].[Salary],
    [e].[SoftDeleted], [e.OtherIdentity].[Id], [e.OtherIdentity].[InActiveUse],
    [e.OtherIdentity].[Name], [e.OtherIdentity].[PrimaryFamilyName],
    [e.OtherIdentity].[PrimaryFirstName], [e.OtherIdentity].[PrimarySSN]
FROM (SELECT * from GetSalaryTable(@p0)) AS [e]
LEFT JOIN [SecondaryIdentity] AS [e.OtherIdentity]
    ON (([e].[SSN] = [e.OtherIdentity].[PrimarySSN])
    AND ([e].[FirstName] = [e.OtherIdentity].[PrimaryFirstName]))
    AND ([e].[FamilyName] = [e.OtherIdentity].[PrimaryFamilyName])
ORDER BY [e].[LastUpdated] DESC
...

组合查询遵循前面示例中所示的相同结构。由于这是一个组合查询，关联数据被加载并显示给用户，如图23-6所示。

图23-6 使用表值函数查询

调用存储过程或其他操作

并非所有存储过程都用于查询数据，这意味着不能始终使用FromSql方法。虽然实体框架核心不会通过 context 类自动公开存储过程，但仍然可以使用它们。清单23-16包含的SQL语句创建了两个存储过程，用于还原或永久删除软删除的数据。在 Visual Studio 【工具】菜单选择【SQL Server】➤【New Query】，连接至数据库，并执行 SQL 以创建函数。

清单 23-16：两个存储过程

USE AdvancedDb
GO

DROP PROCEDURE IF EXISTS RestoreSoftDelete
DROP PROCEDURE IF EXISTS PurgeSoftDelete
GO

CREATE PROCEDURE RestoreSoftDelete
AS
	BEGIN
		UPDATE Employees
		SET SoftDeleted = 0 WHERE SoftDeleted = 1
	END
GO

CREATE PROCEDURE PurgeSoftDelete
AS
	BEGIN
		DELETE from SecondaryIdentity WHERE Id IN
			( SELECT Id from Employees emp
			INNER JOIN SecondaryIdentity ident on ident.PrimarySSN = emp.SSN
			AND ident.PrimaryFirstName = emp.FirstName
			AND ident.PrimaryFamilyName = emp.FamilyName
			WHERE SoftDeleted = 1)
	END
	BEGIN
		DELETE FROM Employees
		WHERE SoftDeleted = 1
	END

使用ExecuteSqlCommand调用不返回数据的存储过程，我在清单23-17中使用该命令更新了Delete控制器。

清单 23-17：Controllers 文件夹下的 DeleteController.cs 文件，调用存储过程

using AdvancedApp.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Collections.Generic;

namespace AdvancedApp.Controllers
{
    public class DeleteController : Controller
    {
        private AdvancedContext context;
        public DeleteController(AdvancedContext ctx) => context = ctx;

        public IActionResult Index()
        {
            return View(context.Employees.Where(e => e.SoftDeleted)
            .Include(e => e.OtherIdentity).IgnoreQueryFilters());
        }

        [HttpPost]
        public IActionResult Restore(Employee employee)
        {
            context.Employees.IgnoreQueryFilters()
            .First(e => e.SSN == employee.SSN
            && e.FirstName == employee.FirstName
            && e.FamilyName == employee.FamilyName).SoftDeleted = false;
            context.SaveChanges();
            return RedirectToAction(nameof(Index));
        }

        [HttpPost]
        public IActionResult Delete(Employee e)
        {
            if (e.OtherIdentity != null)
            {
                context.Remove(e.OtherIdentity);
            }
            context.Employees.Remove(e);
            context.SaveChanges();
            return RedirectToAction(nameof(Index));
        }

        [HttpPost]
        public IActionResult DeleteAll()
        {
            context.Database.ExecuteSqlCommand("EXECUTE PurgeSoftDelete");
            return RedirectToAction(nameof(Index));
        }

        [HttpPost]
        public IActionResult RestoreAll()
        {
            context.Database.ExecuteSqlCommand("EXECUTE RestoreSoftDelete");
            return RedirectToAction(nameof(Index));
        }
    }
}

不返回数据的 SQL 功能通过 context 类的Database.ExecuteSqlCommand方法进行访问。它接受 SQL 字符串（和可选参数）。在清单中，我使用了ExecuteSqlCommand方法来调用清单23-16中定义的存储过程。

清单23-17中的DeleteAll action 已经可以被用户使用，在清单23-18中，我在Delete控制器使用的Index视图中添加了一个 HTML 元素，这样RestoreAll方法就可以成为目标。

清单 23-18：Views/Delete 文件夹下的 Index.cshtml 文件，添加一个元素

@model IEnumerable<Employee>
@{
    ViewData["Title"] = "Advanced Features";
    Layout = "_Layout";
}
<h3 class="bg-info p-2 text-center text-white">Deleted Employees</h3>
<table class="table table-sm table-striped">
    <thead>
        <tr>
            <th>SSN</th>
            <th>First Name</th>
            <th>Family Name</th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <tr class="placeholder"><td colspan="4" class="text-center">No Data</td></tr>
        @foreach (Employee e in Model)
        {
            <tr>
                <td>@e.SSN</td>
                <td>@e.FirstName</td>
                <td>@e.FamilyName</td>
                <td class="text-right">
                    <form method="post">
                        <input type="hidden" name="SSN" value="@e.SSN" />
                        <input type="hidden" name="FirstName" value="@e.FirstName" />
                        <input type="hidden" name="FamilyName"
                               value="@e.FamilyName" />
                        <input type="hidden" name="RowVersion"
                               asp-for="@e.RowVersion" />
                        <input type="hidden" name="OtherIdentity.Id"
                               value="@e.OtherIdentity.Id" />
                        <button asp-action="Restore" class="btn btn-sm btn-success">
                            Restore
                        </button>
                        <button asp-action="Delete" class="btn btn-sm btn-danger">
                            Delete
                        </button>
                    </form>
                </td>
            </tr>
        }
    </tbody>
</table>
<div class="text-center">
    <form method="post" asp-action="DeleteAll">
        <button type="submit" class="btn btn-danger">Delete All</button>
        <button type="submit" class="btn btn-success" asp-action="RestoreAll">
            Restore All
        </button>
    </form>
</div>

要确认正在调用存储过程，请使用dotnet run启动应用程序，导航到 http://localhost:5000，并使用【Delete】按钮软删除Employee对象。然后您可以导航到 http://localhost:5000/delete，并使用【Delete All】或【Restore All】按钮调用存储过程，如图23-7所示。

图23-7 调用存储过程

使用服务器生成的值

在第19章，您看到数据库服务器如何负责生成唯一密钥，但这不是数据库服务器可以创建的唯一值类型。在接下来的部分中，我将解释支持使用数据库服务器生成的数据值的不同 Entity Framework Core 功能。

使用数据库服务器生成的默认值

对于许多属性，使用如第21章所示的具有固定值的HasDefaultValue方法可能很有用，因为它允许您为存储在数据库中的对象定义一个方便的基线。

然而，在某些项目中，要求数据库服务器每次存储新对象时生成默认值是有用的，尽管数据库可以生成的默认值范围有限。例如，我向Employee类添加了一个新属性，如清单23-19所示。除了为每种不同类型的服务器生成的值添加新属性之外，我还添加了一个属性，其目的只是充当占位符，这样我就可以轻松地显示值。

清单 23-19：Models 文件夹下的 Employee.cs 文件，添加属性

using System;

namespace AdvancedApp.Models
{
    public class Employee
    {
        private decimal databaseSalary;
        public long Id { get; set; }
        public string SSN { get; set; }
        public string FirstName { get; set; }
        public string FamilyName { get; set; }
        public decimal Salary
        {
            get => databaseSalary;
            set => databaseSalary = value;
        }
        public SecondaryIdentity OtherIdentity { get; set; }
        public bool SoftDeleted { get; set; } = false;
        public DateTime LastUpdated { get; set; }
        public byte[] RowVersion { get; set; }
        public string GeneratedValue { get; set; } //新添加代码
    }
}

请求数据库服务器为属性生成默认值是使用 Fluent API HasDefaultValueSql方法完成的，该方法类似于HasDefaultValue方法，但告诉数据库服务器执行 SQL 表达式以获得默认值。在清单23-20中，我使用HasDefaultValueSql方法配置了新属性。

清单 23-20：Models 文件夹下的 AdvancedContext.cs 文件，配置默认值

using Microsoft.EntityFrameworkCore;
using System;

namespace AdvancedApp.Models
{
    public class AdvancedContext : DbContext
    {
        public AdvancedContext(DbContextOptions<AdvancedContext> options)
            : base(options) { }
        public DbSet<Employee> Employees { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            modelBuilder.Entity<Employee>() //此处解除注释
             .HasQueryFilter(e => !e.SoftDeleted);
            modelBuilder.Entity<Employee>().Ignore(e => e.Id);
            modelBuilder.Entity<Employee>()
                .HasKey(e => new { e.SSN, e.FirstName, e.FamilyName });
            modelBuilder.Entity<Employee>()
                .Property(e => e.Salary).HasColumnType("decimal(8,2)")
                .HasField("databaseSalary")
                .UsePropertyAccessMode(PropertyAccessMode.Field);
            modelBuilder.Entity<Employee>().Property<DateTime>("LastUpdated")
                .HasDefaultValue(new DateTime(2000, 1, 1));

            modelBuilder.Entity<Employee>() //此处更改
                .Ignore(e => e.RowVersion);
            // .Property(e => e.RowVersion).IsRowVersion();
            //新添加代码
            modelBuilder.Entity<Employee>().Property(e => e.GeneratedValue)
                .HasDefaultValueSql("GETDATE()");

            modelBuilder.Entity<SecondaryIdentity>()
                .HasOne(s => s.PrimaryIdentity)
                .WithOne(e => e.OtherIdentity)
                .HasPrincipalKey<Employee>(e => new {
                    e.SSN,
                    e.FirstName,
                    e.FamilyName
                })
                .HasForeignKey<SecondaryIdentity>(s => new {
                    s.PrimarySSN,
                    s.PrimaryFirstName,
                    s.PrimaryFamilyName
                })
                .OnDelete(DeleteBehavior.Restrict);
            modelBuilder.Entity<SecondaryIdentity>()
                .Property(e => e.Name).HasMaxLength(100);
        }
    }
}

在清单中，我使用了 SQL GETDATE函数来获得时间戳。对 SQL 表达式的限制可用于生成默认值，这意味着值的范围是有限的。例如，不允许引用同一表中的其他列，这是有意义的，因为默认值是在存储数据时设置的。因此，使用 SQL 指定默认值通常涉及调用函数或使用常量。正是由于这个原因，大多数默认值的演示都依赖于我在本例中使用的 SQL GETDATE函数。有更灵活的选择，正如我在下面的章节中所描述的那样。

为了接收GeneratedValue属性的值，我在 Home 控制器的Index action 中更改了查询，使其不再使用GetSalaryTable函数进行查询，此函数返回 Entity Framework Core 需要的值的子集，如清单23-21所示。

清单 23-21：Controllers 文件夹下的 HomeController.cs 文件，修改查询

...
public IActionResult Index()
{
    IEnumerable<Employee> data = context.Employees
    //.FromSql($@"SELECT * from GetSalaryTable({salary})")
    .Include(e => e.OtherIdentity)
    //.OrderByDescending(e => e.Salary)
    .OrderByDescending(e => e.LastUpdated)
    .IgnoreQueryFilters()
    .ToArray();
    ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
    return View(data);
}
...

配置数据库需要进行新的迁移。在 AdvancedApp 项目文件夹中运行清单23-22所示的命令，创建一个名为 GeneratedDefaultValue 的迁移，并将其应用到示例数据库中。 清单 23-22：创建并应用数据库迁移

dotnet ef migrations add GeneratedDefaultValue
dotnet ef database update

要向用户显示默认值，我向 Home 控制器使用的 Index 视图添加了清单23-23所示的元素。

清单 23-23：Views/Home 文件夹下的 Index.cshtml 文件，添加元素

@model IEnumerable<Employee>
@{
    ViewData["Title"] = "Advanced Features";
    Layout = "_Layout";
}
<h3 class="bg-info p-2 text-center text-white">Employees</h3>
<table class="table table-sm table-striped">
    <thead>
        <tr>
            <th>SSN</th>
            <th>First Name</th>
            <th>Family Name</th>
            <th>Salary</th>
            <th>Other Name</th>
            <th>Generated</th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <tr class="placeholder"><td colspan="8" class="text-center">No Data</td></tr>
        @foreach (Employee e in Model)
        {
    <tr>
        <td>@e.SSN</td>
        <td>@e.FirstName</td>
        <td>@e.FamilyName</td>
        <td>@e.Salary</td>
        <td>@(e.OtherIdentity?.Name ?? "(None)")</td>
        <td>@e.GeneratedValue</td>
        <td class="text-right">
            <form>
                <input type="hidden" name="SSN" value="@e.SSN" />
                <input type="hidden" name="Firstname" value="@e.FirstName" />
                <input type="hidden" name="FamilyName"
                       value="@e.FamilyName" />
                <input type="hidden" name="RowVersion"
                       asp-for="@e.RowVersion" />
                <input type="hidden" name="OtherIdentity.Id"
                       value="@e.OtherIdentity?.Id" />
                <button type="submit" asp-action="Delete" formmethod="post"
                        class="btn btn-sm btn-danger">
                    Delete
                </button>
                <button type="submit" asp-action="Edit" formmethod="get"
                        class="btn btn-sm btn-primary">
                    Edit
                </button>
            </form>
        </td>
    </tr>
        }
    </tbody>
</table>
@if (ViewBag.Secondaries != null)
{
    @await Html.PartialAsync("SecondaryIdentities");
}
<div class="text-center">
    <a asp-action="Edit" class="btn btn-primary">Create</a>
</div>

要查看数据库服务器生成的默认值，使用dotnet run启动应用程序，导航至 http://localhost:5000，单击【Create】按钮，存储一个新对象。当存储新对象后，数据库服务器将评估清单23-21中使用的 SQL 表达式，并产生与图23-8所示的结果类似的结果。

图23-8 生成默认值

让数据库服务器生成默认值的一个结果是，Entity Framework Core 必须查询数据库，以找出分配了什么值。如果检查应用程序生成的日志消息，将看到一个INSERT命令用于将新对象存储在数据库中，如下所示：

...
INSERT INTO [Employees] ([SSN], [FirstName], [FamilyName], [Salary], [SoftDeleted])
VALUES (@p0, @p1, @p2, @p3, @p4);
...

插入之后，您将立即看到一个查询，其中有 Entity Framework Core 请求数据库分配的GeneratedValue和GeneratedValue值，如下所示：

...
SELECT [GeneratedValue], [LastUpdated]
FROM [Employees]
WHERE @@ROWCOUNT = 1 AND [SSN] = @p0 AND [FirstName] = @p1 AND [FamilyName] = @p2;
...

Entity Framework Core 使用这些值来更新已存储的对象的属性，这将确保对该对象执行的任何后续操作都具有完整的值集。

合并序列值

增加服务器生成默认值灵活性的一种方法是合并一个序列，其中数据库服务器将根据请求生成唯一值。这类似于我在第19章中描述的生成键值的特性，但它可以应用于任何属性，并且可以包含在生成的值中。

在清单23-24中，我向数据模型添加了一个序列，并将其用作 SQL 表达式的一部分，该表达式为GeneratedValue属性生成一个值。

清单 23-24：Models 文件夹下的 AdvancedContext.cs 文件，使用序列

using Microsoft.EntityFrameworkCore;
using System;

namespace AdvancedApp.Models
{
    public class AdvancedContext : DbContext
    {
        public AdvancedContext(DbContextOptions<AdvancedContext> options)
            : base(options) { }
        public DbSet<Employee> Employees { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            modelBuilder.Entity<Employee>() //此处解除注释
             .HasQueryFilter(e => !e.SoftDeleted);
            modelBuilder.Entity<Employee>().Ignore(e => e.Id);
            modelBuilder.Entity<Employee>()
                .HasKey(e => new { e.SSN, e.FirstName, e.FamilyName });
            modelBuilder.Entity<Employee>()
                .Property(e => e.Salary).HasColumnType("decimal(8,2)")
                .HasField("databaseSalary")
                .UsePropertyAccessMode(PropertyAccessMode.Field);
            modelBuilder.Entity<Employee>().Property<DateTime>("LastUpdated")
                .HasDefaultValue(new DateTime(2000, 1, 1));

            modelBuilder.Entity<Employee>() //此处更改
                .Ignore(e => e.RowVersion);
            // .Property(e => e.RowVersion).IsRowVersion();
            //更改的代码
            modelBuilder.HasSequence<int>("ReferenceSequence")
                .StartsAt(100)
                .IncrementsBy(2);
            modelBuilder.Entity<Employee>().Property(e => e.GeneratedValue)
                .HasDefaultValueSql(@"'REFERENCE_'
                    + CONVERT(varchar, NEXT VALUE FOR ReferenceSequence)");

            modelBuilder.Entity<SecondaryIdentity>()
                .HasOne(s => s.PrimaryIdentity)
                .WithOne(e => e.OtherIdentity)
                .HasPrincipalKey<Employee>(e => new {
                    e.SSN,
                    e.FirstName,
                    e.FamilyName
                })
                .HasForeignKey<SecondaryIdentity>(s => new {
                    s.PrimarySSN,
                    s.PrimaryFirstName,
                    s.PrimaryFamilyName
                })
                .OnDelete(DeleteBehavior.Restrict);
            modelBuilder.Entity<SecondaryIdentity>()
                .Property(e => e.Name).HasMaxLength(100);
        }
    }
}

序列是使用HasSequence方法创建的，其中类型参数设置序列值的数据类型，方法参数是分配给序列的名称。我使用了名称ReferenceSequence，但在实际项目中，您应该尽可能使您的序列名称有意义，因为数据库中的任何地方都可以使用单个序列。

HasSequence方法返回一个SequenceBuilder对象，该对象可用于使用表23-4中描述的方法配置序列。

表 23-4：序列配置方法

名称	描述
StartsAt(value)	此方法用于指定序列的起始值。
IncrementsBy(value)	此方法用于指定在生成值后序列的增量值。
IsCyclic(cycles)	此方法用于指定在达到最大值时序列是否重新开始。
HasMax(value)	此方法用于指定序列的最大值
HasMin(value)	此方法用于指定序列的最小值

在清单中，我使用StartsAt方法指定 100 作为起始值，使用IncrementsBy方法创建一个序列，在每个新值生成后增加 2。为了使用序列，我更改了传递给HasDefaultValueSql方法的表达式，以便将序列的下一个值转换为字符串并以REFERENCE_作为前缀。

应用序列需要进行新的迁移。在 AdvancedApp 项目文件夹中运行清单23-25所示的命令，创建名为Sequence的迁移并将其应用于数据库。

清单 23-25：创建并应用迁移

dotnet ef migrations add Sequence
dotnet ef database update

要查看效果，请使用dotnet run启动应用程序，导航到 http://localhost:5000，单击【Create按钮】，并将一个新对象存储在数据库中。当显示结果时，您将看到序列已用于为新对象创建默认值，如图23-9所示。

图23-9 使用序列生成默认值

避免序列陷阱

在使用序列时有两个常见的缺陷。第一个缺陷是在使用表23-4中描述的方法时限制范围太小，导致可用值池太小，无法满足应用程序的需要。在开发之后的某个时间，将使用所有可能的序列值，并且数据库服务器将开始报告错误。

第二个陷阱是纠正耗尽序列的最常见尝试，即快速应用IsCyclic方法，使序列重新开始。循环序列中的值不能保证是唯一的，这意味着相同的值可能会被分配几次，并对假定序列值是唯一的应用程序造成问题。

在创建序列时，请选择为应用程序提供足够大范围的值的数据类型。如果您需要唯一的值，那么考虑使用备用键，如第19章所述。

计算数据库中的值

计算列是数据库服务器使用数据库中已经存在的值计算的列。这可能是生成值的有用方法，否则每次执行查询时都必须反复计算，或者需要客户端查询计算。

例如，我在 Home 控制器的Index action 中更改了查询，以便用户可以应用一个搜索项来搜索组合的FirstName和FamilyName值，如清单23-26所示。

清单 23-26：Controllers 文件夹下的 HomeController.cs 文件，使用搜索项查询

...
public IActionResult Index(string searchTerm)
{
    IQueryable<Employee> query = context.Employees.Include(e => e.OtherIdentity);
    if (!string.IsNullOrEmpty(searchTerm))
    {
        query = query.Where(e => EF.Functions
            .Like($"{e.FirstName[0]}{e.FamilyName}", searchTerm));
    }
    IEnumerable<Employee> data = query.ToArray();
    ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
    return View(data);
}
...

查询接受FirstName属性的第一个字符，将其与FamilyName属性的值连接起来，并使用LIke的方法执行搜索。使用dotnet run启动应用程序，然后导航到 http://localhost:5000?searchterm=%ajon%。搜索项是%ajon%，它将与诸如Alice Jones等员工匹配，但您可能需要更改搜索项，以匹配存储的数据。您将在应用程序生成的日志消息中看到以下警告：

...
The LINQ expression 'where __Functions_0.Like(Format("{0}{1}", [e].FamilyName, Convert([e].
FirstName.get_Chars(0), Object)), __searchTerm_1)' could not be translated and will be
evaluated locally.
...

正如我在第20章中解释的那样，如果查询不能转换为 SQL，那么将在客户机中计算Like方法，这就是这个示例的结果。

计算列可以通过生成可以在数据库中查询的值来帮助避免客户端计算。在清单23-27中，我更改了GeneratedValue属性的配置，以便它是一个计算列，它将包含组合的名称值。

清单 23-27：Models 文件夹下的 AdvancedContext.cs 文件，定义计算列

using Microsoft.EntityFrameworkCore;
using System;

namespace AdvancedApp.Models
{
    public class AdvancedContext : DbContext
    {
        public AdvancedContext(DbContextOptions<AdvancedContext> options)
            : base(options) { }
        public DbSet<Employee> Employees { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            modelBuilder.Entity<Employee>() 
             .HasQueryFilter(e => !e.SoftDeleted);
            modelBuilder.Entity<Employee>().Ignore(e => e.Id);
            modelBuilder.Entity<Employee>()
                .HasKey(e => new { e.SSN, e.FirstName, e.FamilyName });
            modelBuilder.Entity<Employee>()
                .Property(e => e.Salary).HasColumnType("decimal(8,2)")
                .HasField("databaseSalary")
                .UsePropertyAccessMode(PropertyAccessMode.Field);
            modelBuilder.Entity<Employee>().Property<DateTime>("LastUpdated")
                .HasDefaultValue(new DateTime(2000, 1, 1));

            modelBuilder.Entity<Employee>() 
                .Ignore(e => e.RowVersion);
            // .Property(e => e.RowVersion).IsRowVersion();
            modelBuilder.HasSequence<int>("ReferenceSequence")
                .StartsAt(100)
                .IncrementsBy(2);
            //更改的代码
            modelBuilder.Entity<Employee>().Property(e => e.GeneratedValue)
                //.HasDefaultValueSql(@"'REFERENCE_'
                // + CONVERT(varchar, NEXT VALUE FOR ReferenceSequence)");
                .HasComputedColumnSql(@"SUBSTRING(FirstName, 1, 1)
                    + FamilyName PERSISTED");
            modelBuilder.Entity<Employee>().HasIndex(e => e.GeneratedValue);

            modelBuilder.Entity<SecondaryIdentity>()
                .HasOne(s => s.PrimaryIdentity)
                .WithOne(e => e.OtherIdentity)
                .HasPrincipalKey<Employee>(e => new {
                    e.SSN,
                    e.FirstName,
                    e.FamilyName
                })
                .HasForeignKey<SecondaryIdentity>(s => new {
                    s.PrimarySSN,
                    s.PrimaryFirstName,
                    s.PrimaryFamilyName
                })
                .OnDelete(DeleteBehavior.Restrict);
            modelBuilder.Entity<SecondaryIdentity>()
                .Property(e => e.Name).HasMaxLength(100);
        }
    }
}

计算列使用HasComputedColumnSql方法进行配置，该方法接收将用于生成属性值的 SQL 表达式。在本例中，SQL 表达式创建与前面使用的名称相同的级联名称。

...
.HasComputedColumnSql(@"SUBSTRING(FirstName, 1, 1) + FamilyName PERSISTED");
...

PERSISTED关键字告诉数据库服务器永久存储数据库中的值，而不是为每个查询生成这些值。我还使用Fluent API HasIndex方法为GeneratedValue属性创建了一个索引；这不是计算列的要求，但它将提高搜索性能。

添加计算列需要对数据库进行更新。在 AdvancedApp 项目文件夹中运行清单23-28所示的命令，以创建新的迁移并将其应用于数据库。

提示：数据库服务器将在任何依赖于更改的值时自动重新计算的列值。在本例中，这意味着如果FirstName或FamilyName值发生变化，将重新计算SearchName属性。

清单23-28：创建并应用数据库迁移

dotnet ef migrations add ComputedColumn
dotnet ef database update

使用计算列进行查询

一旦定义了计算列，就可以在与任何其他列一样的查询中使用。在清单23-29中，我已经在 Home 控制器的Index action 中更新了查询，使得对与计算列相对应的属性执行类似的操作。

清单 23-29：Controllers 文件夹下的 HomeController.cs 文件，使用计算列

...
public IActionResult Index(string searchTerm)
{
    IQueryable<Employee> query = context.Employees.Include(e => e.OtherIdentity);
    if (!string.IsNullOrEmpty(searchTerm))
    {
        query = query.Where(e => EF.Functions.Like(e.GeneratedValue, searchTerm));
    }
    IEnumerable<Employee> data = query.ToArray();
    ViewBag.Secondaries = data.Select(e => e.OtherIdentity);
    return View(data);
}
...

使用dotnet run启动应用程序，导航至 http://localhost:5000?searchTerm=%ajon%；您将看到图23-10所示的结果，尽管可能需要更改搜索项以匹配数据库中的数据。如果检查应用程序生成的日志消息，将看到类似的操作是作为 SQL 查询的一部分执行的。

...
SELECT [e].[SSN], [e].[FirstName], [e].[FamilyName], [e].[GeneratedValue],
    [e].[LastUpdated], [e].[Salary], [e].[SoftDeleted], [e.OtherIdentity].[Id],
    [e.OtherIdentity].[InActiveUse], [e.OtherIdentity].[Name],
    [e.OtherIdentity].[PrimaryFamilyName], [e.OtherIdentity].[PrimaryFirstName],
    [e.OtherIdentity].[PrimarySSN]
FROM [Employees] AS [e]
LEFT JOIN [SecondaryIdentity] AS [e.OtherIdentity]
    ON (([e].[SSN] = [e.OtherIdentity].[PrimarySSN])
        AND ([e].[FirstName] = [e.OtherIdentity].[PrimaryFirstName]))
        AND ([e].[FamilyName] = [e.OtherIdentity].[PrimaryFamilyName])
WHERE ([e].[SoftDeleted] = 0) AND [e].[GeneratedValue] LIKE @__searchTerm_1
...

图23-10 使用计算列查询

为生成值自动建模

在某些项目中，您可能必须处理由数据库服务器生成但尚未在数据库中配置的值。这通常是因为数据库包含触发器，在数据库中插入或修改数据时，触发器会自动生成值。

为了准备这个例子，在 AdvancedApp 项目文件夹中运行清单23-30所示的命令。这些命令从项目中删除最近的迁移并重新创建数据库，这避免了 Entity Framework Core 在索引仍在使用时尝试删除列的问题。

清单 23-30：移除迁移并删除数据库

dotnet ef database drop --force
dotnet ef migrations remove --force
dotnet ef database update

清单23-31包含创建简单触发器所需的 SQL 语句，该触发器在数据库中存储或更新Employee对象时更新GeneratedValue属性。从 Visual Studio 【工具】菜单中选择【SQL Server】➤【New Query】，连接到数据库，并执行 SQL 创建触发器。

清单 23-31：在数据库中创建触发器

USE AdvancedDb
GO

DROP TRIGGER IF EXISTS GeneratedValueTrigger
GO

CREATE TRIGGER GeneratedValueTrigger ON Employees
	AFTER INSERT, UPDATE
AS
BEGIN
	DECLARE @Salary decimal(8,0), @SSN nvarchar(450),
		@First nvarchar(450), @Family nvarchar(450)

	SELECT @Salary = INSERTED.Salary, @SSN = INSERTED.SSN,
		@First = INSERTED.FirstName, @Family = INSERTED.FamilyName
	FROM INSERTED

	UPDATE dbo.Employees SET GeneratedValue = FLOOR(@Salary /2)
	WHERE SSN = @SSN AND FirstName = @First AND FamilyName = @Family
END

此触发器将在 Employee 表中的一行正在更新，并将GeneratedValue属性设置为Salary值的一半时执行。

Entity Framework Core 不支持在数据库中设置类似触发器的功能，但您可以确保它检查数据库服务器生成的值，以便使用相同对象执行的后续操作不会有不完整或陈旧的数据。

有四个 Fluent API 方法可以用来告诉 Entity Framework Core 如何为属性生成值，如表23-5所述。

表 23-5：生成属性值的 Fluent API 方法

名称	描述
ValueGeneratedNever()	此方法告诉 Entity Framework Core，将永远不会为该属性生成值，这是默认行为。
ValueGeneratedOnAdd()	此方法告诉 Entity Framework Core，当新对象存储在数据库中时，将为该属性生成值。
ValueGeneratedOnUpdate()	此方法告诉 Entity Framework Core，在更新现有对象时将为该属性生成值。
ValueGeneratedOnAddOrUpdate()	此方法告诉 Entity Framework Core，在数据库中存储新对象或更新现有对象时将生成值。

我将在数据库中添加一个触发器，在 SecondaryIdentity 表中插入或更新一行时，该触发器将为GeneratedValue列生成一个值。为了配置数据模型，我更改了GenerateValue属性的配置，以删除计算列，并告诉 Entity Framework Core 在插入或更新数据时查询生成的值，如清单23-32所示。

理解生成值方法的局限性

表23-5中描述的方法没有将数据库配置为生成值；它们只告诉 Entity Framework Core 何时应该查询以获得已经配置的生成值。这意味着这些方法对于现有数据库的建模非常有用，但是在代码优先项目中使用时不会产生效果。

即使在数据库优先项目中，这些方法也只有在需要使用相同的数据库 context 对象（例如修复功能）进行一系列查询时才有用。在大多数 ASP.NET Core MVC 应用程序中，如果在执行创建或更新操作后放弃 context 及其缓存数据，则在使用表23-5中的方法时，Entity Framework Core 所做的其他查询无效。

清单 23-32：Models 文件夹下的 AdvancedContext.cs 文件，配置属性

using Microsoft.EntityFrameworkCore;
using System;

namespace AdvancedApp.Models
{
    public class AdvancedContext : DbContext
    {
        public AdvancedContext(DbContextOptions<AdvancedContext> options)
            : base(options) { }
        public DbSet<Employee> Employees { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            modelBuilder.Entity<Employee>() 
             .HasQueryFilter(e => !e.SoftDeleted);
            modelBuilder.Entity<Employee>().Ignore(e => e.Id);
            modelBuilder.Entity<Employee>()
                .HasKey(e => new { e.SSN, e.FirstName, e.FamilyName });
            modelBuilder.Entity<Employee>()
                .Property(e => e.Salary).HasColumnType("decimal(8,2)")
                .HasField("databaseSalary")
                .UsePropertyAccessMode(PropertyAccessMode.Field);
            modelBuilder.Entity<Employee>().Property<DateTime>("LastUpdated")
                .HasDefaultValue(new DateTime(2000, 1, 1));

            modelBuilder.Entity<Employee>() 
                .Ignore(e => e.RowVersion);
            // .Property(e => e.RowVersion).IsRowVersion();
            modelBuilder.HasSequence<int>("ReferenceSequence")
                .StartsAt(100)
                .IncrementsBy(2);
            //更改的代码
            modelBuilder.Entity<Employee>().Property(e => e.GeneratedValue)
                .ValueGeneratedOnAddOrUpdate();

            modelBuilder.Entity<SecondaryIdentity>()
                .HasOne(s => s.PrimaryIdentity)
                .WithOne(e => e.OtherIdentity)
                .HasPrincipalKey<Employee>(e => new {
                    e.SSN,
                    e.FirstName,
                    e.FamilyName
                })
                .HasForeignKey<SecondaryIdentity>(s => new {
                    s.PrimarySSN,
                    s.PrimaryFirstName,
                    s.PrimaryFamilyName
                })
                .OnDelete(DeleteBehavior.Restrict);
            modelBuilder.Entity<SecondaryIdentity>()
                .Property(e => e.Name).HasMaxLength(100);
        }
    }
}

更改属性的配置需要更新数据库。在 AdvancedApp 文件夹中运行清单23-33中所示的命令，以创建新的迁移并将其应用到数据库。

清单 23-33：创建并应用迁移

dotnet ef migrations add AutomaticallyGenerated
dotnet ef database update

使用dotnet run启动应用程序，导航至 http://localhost:5000，单击【Create】按钮，存储一个新的Employee对象。当数据存储时，触发器将为数据库中的新行设置GeneratedValue列，产生如图23-11所示的结果。

图23-11 查询自动生成的值

但是，对于这个示例来说，重要的是 Entity Framework Core 在执行INSERT或UPDATE命令后将查询GeneratedValue。在应用程序生成的日志消息中，您将看到用于存储所创建对象的INSERT语句，如下所示：

...
INSERT INTO [Employees] ([SSN], [FirstName], [FamilyName], [Salary], [SoftDeleted])
VALUES (@p0, @p1, @p2, @p3, @p4);
...

在此命令之后，您将看到一个查询，该查询将获取数据库服务器生成的属性值，如下所示：

...
SELECT [GeneratedValue], [LastUpdated]
FROM [Employees]
WHERE @@ROWCOUNT = 1 AND [SSN] = @p0 AND [FirstName] = @p1 AND [FamilyName] = @p2;
...

使用属性对生成的值进行建模

如果不喜欢使用 Fluent Api，那么您可以告诉 Entity Framework Core 关于数据库服务器使用DatabaseGenerated特性为其生成值的属性，该特性接受来自DatabaseGeneratedOption枚举的值作为其参数，如下所示：

...
[DatabaseGenerated(Computed)]
public string GeneratedValue { get; set; }
...

有三个DatabaseGeneratedOption值可用。None值表示数据库永远不会生成值，Identity选项表示存储新对象时数据库将生成值，Computed值告诉 Entity Framework Core，数据库将在存储新对象或更新现有对象时生成值。

总结

在本章中，我描述了直接使用 SQL 的 Entity Framework Core 功能。我向您展示了如何将 SQL 直接包含在查询中，以及如何使用不同的数据库服务器功能（包括视图、存储过程和表值函数）创建复杂查询。我还解释了数据库服务器生成数据值的不同方式，以及如何将这些数据值合并到应用程序中。在下一章中，我将解释 Entity Framework Core 如何支持事务。

;

第 23 章 使用数据库服务器功能